package com.base.excel.jxl.export;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import com.base.utils.StringUtil;
import com.base.utils.VerifyUtil;

/**
 * <p>JXL导出处理类</p>
 * @author Jason
 */
public class JxlExportHandler {
	
	public static final JxlExportHandler exportHandler = new JxlExportHandler();
	
	private JxlExportHandler() {}
	
	public static JxlExportHandler getInstance() {
		return exportHandler;
	}
	
	/**
	 * <p>导出Excel</p>
	 * @param param
	 * @param os
	 * @throws RowsExceededException
	 * @throws WriteException
	 * @throws IOException
	 */
	public void exportExcel(JxlExportParam param, OutputStream os) throws RowsExceededException, WriteException, IOException {
		WritableWorkbook workbook = Workbook.createWorkbook(os);
		writeExcel(param, workbook);
	}
	
	/**
	 * <p>写入excel</p>
	 * @param data 需要写入的数据
	 * @param writeBook jxl写入对象
	 * @throws WriteException 
	 * @throws RowsExceededException 
	 * @throws IOException
	 */
	private void writeExcel(JxlExportParam param, WritableWorkbook writeBook) throws RowsExceededException, WriteException, IOException {
		if(!VerifyUtil.isNotNull(param)) {
			return;
		}
		//Sheet个数
		List<Sheet> sheetStrList = param.getSheetList();
		if(!VerifyUtil.listNotEmpty(sheetStrList)) {
			return;
		}
		for (int i = 0; i < sheetStrList.size(); i++) {
			Sheet sheet = sheetStrList.get(i);
			//jxl Sheet对象
			WritableSheet wsheet = writeBook.createSheet(sheet.getName(), i);
			//设置第一行(列头)高度
			wsheet.setRowView(0, 300);
			/*
			 * 定义单元格样式
			 */
			// 定义格式 字体 下划线 斜体 粗体 颜色
		    WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
		              WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); 
		    // 单元格定义
		    WritableCellFormat wcf = new WritableCellFormat(wf); 
		    // 设置单元格的背景颜色
		    wcf.setBackground(Colour.PALE_BLUE); 
		    // 设置对齐方式
//		    wcf.setAlignment(jxl.format.Alignment.CENTRE); 
		    // 设置边框
		    wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
		    
			//获得Sheet头部数据
			String[] headerArr = sheet.getHeaderArr();
			//获得Sheet列数据
			List<String[]> dataList = sheet.getDataList();
			for (int j = 0; j < headerArr.length; j++) {
				String name = headerArr[j];
				//设置列宽
				wsheet.setColumnView(j, StringUtil.stringLen(name) + 10);
				/*
				 * 标签
				 * 第一个参数：行
				 * 第二个参数：列
				 * 第三个参数：名称 
				 */
				Label label = new Label(j, 0, name, wcf);
				//添加单元格
				wsheet.addCell(label);
			}
			for (int j = 1; j <= dataList.size(); j++) {
				String[] dataArr = dataList.get((j - 1));
				for (int k = 0; k < dataArr.length; k++) {
					String name = dataArr[k];
					Label label = new Label(k, j, name);
					//添加列
					wsheet.addCell(label);
				}
			}
		}
		//写入流
		writeBook.write();
		writeBook.close();
	}
	
	public static void main(String[] args) {
		JxlExportHandler handler = new JxlExportHandler();
		JxlExportParam param = new JxlExportParam();
		String[] header = {"测试1", "测试2"};
		List<String[]> dataList = new ArrayList<String[]>();
		String[] dataArr1 = {"NND", "MMD"};
		String[] dataArr2 = {"NND", "MMD"};
		dataList.add(dataArr1);
		dataList.add(dataArr2);
		Sheet sheet = new Sheet();
		sheet.setName("测试Sheet1");
		sheet.setHeaderArr(header);
		sheet.setDataList(dataList);
		
		Sheet sheet2 = new Sheet();
		sheet2.setName("测试Sheet2");
		sheet2.setHeaderArr(header);
		sheet2.setDataList(dataList);
		
		List<Sheet> sheetList = new ArrayList<Sheet>();
		sheetList.add(sheet);
		sheetList.add(sheet2);
		param.setSheetList(sheetList);
		try {
			FileOutputStream os = new FileOutputStream("d:\\test.xls");
			WritableWorkbook workbook = Workbook.createWorkbook(os);
			handler.writeExcel(param, workbook);
		} catch (Exception e) {
			e.printStackTrace();
		}		
	}
}
